package dao.impl;

import com.mysql.cj.util.StringUtils;
import dao.BaseDao;
import dao.RepairDao;
import entity.Baoxiu;

import javax.naming.NamingException;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class RepairDaoImpl extends BaseDao implements RepairDao {

    @Override
    public int getTotalCount(Integer id, String roomNumber)
            throws SQLException, NamingException, ClassNotFoundException {
        getConnection();
        PreparedStatement ps = null;
        ResultSet rs = null;

        try {

            StringBuilder sql = new StringBuilder(
                    "SELECT COUNT(*) FROM repair_orders d " +
                            "JOIN students s ON d.student_id = s.student_id " +
                            "JOIN dorm_rooms r ON s.room_id = r.room_id");

            if (id != null) {
                sql.append(" WHERE d.order_id = ?");
            } else if (!StringUtils.isNullOrEmpty(roomNumber)) {
                sql.append(" WHERE r.room_number = ?");
            }

            ps = conn.prepareStatement(sql.toString());
            if (id != null) {
                ps.setInt(1, id);
            } else if (!StringUtils.isNullOrEmpty(roomNumber)) {
                ps.setString(1, roomNumber);
            }

            rs = ps.executeQuery();
            return rs.next() ? rs.getInt(1) : 0;
        } finally {
            if (rs != null) rs.close();
            if (ps != null) ps.close();
            if (conn != null) conn.close();
        }
    }

    @Override
    public List<Baoxiu> chaxun(Integer id, String roomNumber, int page, int pageSize)
            throws SQLException, NamingException, ClassNotFoundException {
        getConnection();
        PreparedStatement ps = null;
        ResultSet rs = null;
        List<Baoxiu> list = new ArrayList<>();

        try {

            StringBuilder sql = new StringBuilder(
                    "SELECT d.order_id, s.name, d.description, d.created_at, " +
                            "d.process_at, d.completed_at, d.status, r.room_number " +
                            "FROM repair_orders d " +
                            "JOIN students s ON d.student_id = s.student_id " +
                            "JOIN dorm_rooms r ON s.room_id = r.room_id");

            if (id != null) {
                sql.append(" WHERE d.order_id = ?");
            } else if (!StringUtils.isNullOrEmpty(roomNumber)) {
                sql.append(" WHERE r.room_number = ?");
            }
            sql.append(" LIMIT ?, ?");

            ps = conn.prepareStatement(sql.toString());
            int paramIndex = 1;
            if (id != null) {
                ps.setInt(paramIndex++, id);
            } else if (!StringUtils.isNullOrEmpty(roomNumber)) {
                ps.setString(paramIndex++, roomNumber);
            }

            int startPage=page*pageSize;
            ps.setInt(paramIndex++, startPage);
            ps.setInt(paramIndex, pageSize);

            rs = ps.executeQuery();

            while (rs.next()) {
                Baoxiu baoxiu = new Baoxiu();
                baoxiu.setOrderId(rs.getInt("order_id"));
                baoxiu.setRoomNumber(rs.getString("room_number"));
                baoxiu.setName(rs.getString("name"));
                baoxiu.setDescription(rs.getString("description"));
                baoxiu.setCreatedAt(rs.getString("created_at"));
                baoxiu.setProcessAt(rs.getString("process_at"));
                baoxiu.setCompletedAt(rs.getString("completed_at"));
                baoxiu.setStatus(rs.getString("status"));
                list.add(baoxiu);
            }
            return list;
        } finally {
            if (rs != null) rs.close();
            if (ps != null) ps.close();
            if (conn != null) conn.close();
        }
    }

    @Override
    public int tianjia(Baoxiu baoxiu)
            throws SQLException, NamingException, ClassNotFoundException {
        getConnection();
        PreparedStatement ps = null;

        try {

            String sql = "INSERT INTO repair_orders(student_id, description, status) VALUES(?, ?, ?)";
            ps = conn.prepareStatement(sql);
            ps.setInt(1, baoxiu.getStudentId());
            ps.setString(2, baoxiu.getDescription());
            ps.setString(3, baoxiu.getStatus());
            return ps.executeUpdate();
        } finally {
            if (ps != null) ps.close();
            if (conn != null) conn.close();
        }
    }

    @Override
    public Integer tjpd(Baoxiu baoxiu) throws SQLException, ClassNotFoundException {
        getConnection();
        PreparedStatement ps = null;
        ResultSet rs = null;

        try {

            String sql = "SELECT student_id FROM students WHERE name = ? " +
                    "AND room_id = (SELECT room_id FROM dorm_rooms WHERE room_number = ? LIMIT 1)";
            ps = conn.prepareStatement(sql);
            ps.setString(1, baoxiu.getName());
            ps.setString(2, baoxiu.getRoomNumber());
            rs = ps.executeQuery();
            return rs.next() ? rs.getInt("student_id") : null;
        } finally {
            if (rs != null) rs.close();
            if (ps != null) ps.close();
            if (conn != null) conn.close();
        }
    }

    @Override
    public int updateStatus(int orderId, String status, String processAt, String completedAt)
            throws SQLException, NamingException, ClassNotFoundException {
        getConnection();
        PreparedStatement ps = null;

        try {

            StringBuilder sql = new StringBuilder("UPDATE repair_orders SET status = ?");

            if (processAt != null) {
                sql.append(", process_at = ?");
            } else if (completedAt != null) {
                sql.append(", completed_at = ?");
            }
            sql.append(" WHERE order_id = ?");

            ps = conn.prepareStatement(sql.toString());
            int paramIndex = 1;
            ps.setString(paramIndex++, status);
            if (processAt != null) {
                ps.setString(paramIndex++, processAt);
            } else if (completedAt != null) {
                ps.setString(paramIndex++, completedAt);
            }
            ps.setInt(paramIndex, orderId);

            return ps.executeUpdate();
        } finally {
            if (ps != null) ps.close();
            if (conn != null) conn.close();
        }
    }
}